<?php
/**
 * (logic)
 */
namespace App\Logic\Operation;

use App\Models\Amazon\AmazonOrderItem;
use App\Models\Basics\AmazonCountry;
use App\Models\Basics\SysICountry;
use App\Models\Basics\Transport;
use App\Models\MonthlyReport\Cost\LogisticsBasics;
use App\Models\MonthlyReport\Payment\PaymentFBAFee;
use App\Models\Operation\ProfitSku;
use App\Models\Product\ProductAmazonSku;
use App\Models\Product\ProductAmazonSkuCCS;
use App\Models\Product\ProductSku;
use App\Models\SalesReport\PaymentClassify;
use Illuminate\Support\Facades\DB;
class SyncProfitSku
{
    /**
     * 同步产品销售（产品毛利率表销售）
     */
    public static function syncSeller(){
        $exist = [];
        $classify = [];
        ProfitSku::query()
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$exist,&$classify){
                DB::beginTransaction();
                try{
                    foreach ($items as $item){
                        if(!array_key_exists($item->psku_id,$classify)){
                            $classify[$item->psku_id] = ProductSku::getValueById($item->psku_id,'classify_id');
                        }
                        $classifyId = $classify[$item->psku_id];
                        if(!$classifyId)continue;
                        $key = $item->country_id .'_'.$classifyId;
                        if(!array_key_exists($key,$exist)){
                            $exist[$key] = ProductAmazonSkuCCS::getSellerId($item->country_id,$classifyId);
                        }
                        $item->seller_id = $exist[$key];
                        $item->save();
                    }
                    DB::commit();
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }

    /**
     * 同步产品售价
     */
    public static function syncSalePrice(){
        $exist = [];
        $marketplace = [];
        ProfitSku::query()
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$marketplace,&$exist){
                DB::beginTransaction();
                try{
                    foreach ($items as $item){
                        if(!array_key_exists($item->country_id,$marketplace)){
                            $marketplace[$item->country_id] = AmazonCountry::getMarketplaceIdByCountryId($item->country_id);
                        }
                        $marketplaceId = $marketplace[$item->country_id];
                        if(!$marketplaceId)continue;
                        $key = $item->country_id . '_' . $item->psku_id;
                        if(!array_key_exists($key,$exist)){
                            $exist[$key] = self::_getSalePrice($item->country_id,$marketplaceId,$item->psku_id);
                        }
                        if(floatval($exist[$key]) == 0)continue;
                        $tpl = round($item->tpl_rmb / $item->rate_odds,3);
                        $item->sale_price = $exist[$key];
                        $item->cost = ProfitSku::getCost($tpl,$item->sale_price,$item->head_cost,$item->fba_fee,$item->commission,$item->return,$item->vat);
                        $item->profit = round($item->sale_price - $item->cost,3);
                        $item->profit_rate = $item->sale_price == 0 ? 0 : round($item->profit / $item->sale_price,3);
                        $fbaFeeFlag = $item->country_id == 20 ? false : true;
                        $item->status = ProfitSku::getStatus($item->status,$item->sale_price,$item->fba_fee,$item->commission,$fbaFeeFlag,$item->created_at);
                        $item->save();
                    }
                    DB::commit();
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }

    private static function _getSalePrice($countryId,$marketplaceId,$pSkuId){
        $sellerSku = ProductAmazonSku::query()
            ->where('status',1)
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->pluck('seller_sku')
            ->toArray();
        if(empty($sellerSku))return 0;
        $item = AmazonOrderItem::query()
            ->whereNotNull('price')
            ->where('price','<>',0)
            ->where('quantity','<>',0)
            ->whereIn('sku',array_unique($sellerSku))
            ->whereHas('order', function ($query) use ($marketplaceId){
                $query->where('marketplace_id',$marketplaceId);
            })
            ->orderBy('id','DESC')
            ->first();
        return $item ? (($item->price ?: 0) / $item->quantity) : 0;
    }

    /**
     * 同步FBA费
     */
    public static function syncFBAFee(){
        ini_set('memory_limit', '200M');
        set_time_limit(0);
        $exist = [];
        $sellerSkuArr = [];
        ProfitSku::query()
            ->orderBy('id')
            ->chunk(800, function ($items) use (&$exist,&$sellerSkuArr){
                DB::beginTransaction();
                try{
                    $reportDate = PaymentFBAFee::query()->groupBy('report_date')->orderBy('report_date','DESC')->pluck('report_date');
                    $sql = "UPDATE operation_profit_sku SET ";
                    $idArr = [];
                    $sql1 = "fba_fee = CASE id ";
                    $sql2 = "cost = CASE id ";
                    $sql3 = "profit = CASE id ";
                    $sql4 = "profit_rate = CASE id ";
                    $sql5 = "status = CASE id ";
                    foreach ($items as $item){
                        $key = $item->country_id .'_'.$item->psku_id;
                        if(!array_key_exists($key,$sellerSkuArr)){
                            $sellerSkuArr[$key] = ProductAmazonSku::query()
                                ->where('country_id',$item->country_id)
                                ->where('psku_id',$item->psku_id)
                                ->pluck('seller_sku')
                                ->toArray();
                        }
                        $sellerSku = $sellerSkuArr[$key];
                        if(empty($sellerSku))continue;
                        if(!array_key_exists($key,$exist)){
                            $exist[$key] = self::_getFBAFee($reportDate,$item->country_id,array_unique($sellerSku));
                        }
                        if(floatval($exist[$key]) == 0)continue;
                        $tpl = round($item->tpl_rmb / $item->rate_odds,3);
                        $fbaFee = $exist[$key];
                        $cost = ProfitSku::getCost($tpl,$item->sale_price,$item->head_cost,$fbaFee,$item->commission,$item->return,$item->vat);
                        $profit = round($item->sale_price - $cost,3);
                        $profitRate = $item->sale_price == 0 ? 0 : round($profit / $item->sale_price,3);
                        $fbaFeeFlag = $item->country_id == 20 ? false : true;
                        $status = ProfitSku::getStatus($item->status,$item->sale_price,$fbaFee,$item->commission,$fbaFeeFlag,$item->created_at);
                        $sql1 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $fbaFee);
                        $sql2 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $cost);
                        $sql3 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $profit);
                        $sql4 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $profitRate);
                        $sql5 .= sprintf("WHEN %d THEN %d ", $item->id, $status);
                        $idArr[] = $item->id;
                    }
                    if(!empty($idArr)){
                        $ids = implode(',',$idArr);
                        $sql1 .= "END,";
                        $sql2 .= "END,";
                        $sql3 .= "END,";
                        $sql4 .= "END,";
                        $sql5 .= "END";
                        $sql .= $sql1 . $sql2 . $sql3 . $sql4 . $sql5 . " WHERE id IN ($ids)";
                        DB::update(DB::raw($sql));
                    }
                    DB::commit();
                    sleep(10);
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }

    /**
     * 同步FBA费Daily
     */
    public static function syncFBAFeeDaily(){
        $exist = [];
        $sellerSkuArr = [];
        $time = date('Y-m-01 08:00:00',strtotime('-1 days'));
        ProfitSku::query()
            ->where('created_at','>=',$time)
            ->orderBy('id')
            ->chunk(800, function ($items) use (&$exist,&$sellerSkuArr){
                DB::beginTransaction();
                try{
                    $reportDate = PaymentFBAFee::query()->groupBy('report_date')->orderBy('report_date','DESC')->pluck('report_date');
                    foreach ($items as $item){
                        $key = $item->country_id .'_'.$item->psku_id;
                        if(!array_key_exists($key,$sellerSkuArr)){
                            $sellerSkuArr[$key] = ProductAmazonSku::query()
                                ->where('country_id',$item->country_id)
                                ->where('psku_id',$item->psku_id)
                                ->pluck('seller_sku')
                                ->toArray();
                        }
                        $sellerSku = $sellerSkuArr[$key];
                        if(empty($sellerSku))continue;
                        if(!array_key_exists($key,$exist)){
                            $exist[$key] = self::_getFBAFee($reportDate,$item->country_id,array_unique($sellerSku));
                        }
                        if(floatval($exist[$key]) == 0)continue;
                        $tpl = round($item->tpl_rmb / $item->rate_odds,3);
                        $item->fba_fee = $exist[$key];
                        $item->cost = ProfitSku::getCost($tpl,$item->sale_price,$item->head_cost,$item->fba_fee,$item->commission,$item->return,$item->vat);
                        $item->profit = round($item->sale_price - $item->cost,3);
                        $item->profit_rate = $item->sale_price == 0 ? 0 : round($item->profit / $item->sale_price,3);
                        $fbaFeeFlag = $item->country_id == 20 ? false : true;
                        $item->status = ProfitSku::getStatus($item->status,$item->sale_price,$item->fba_fee,$item->commission,$fbaFeeFlag,$item->created_at);
                        $item->save();
                    }
                    DB::commit();
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }

    private static function _getFBAFee($reportDate,$countryId,$sellerSku){
        $fee = 0;
        foreach ($reportDate as $_reportDate){
            $count = PaymentFBAFee::query()
                ->where('report_date',$_reportDate)
                ->whereNotNull('purchase_date')
                ->where('country_id',$countryId)
                ->whereIn('sku',$sellerSku)
                ->count();
            if($count != 0){
                $fee = PaymentFBAFee::query()
                    ->where('report_date',$_reportDate)
                    ->whereNotNull('purchase_date')
                    ->where('country_id',$countryId)
                    ->whereIn('sku',$sellerSku)
                    ->orderBy('purchase_date','DESC')
                    ->max('fba_fees');
                break;
            }else{
                continue;
            }
        }
        return $fee ? abs($fee) : 0;
    }

    /**
     * 同步退货率
     */
    public static function syncReturn(){
        $exist = [];
        $classify = [];
        ProfitSku::query()
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$exist,&$classify){
                DB::beginTransaction();
                try{
                    foreach ($items as $item){
                        if(!array_key_exists($item->psku_id,$classify)){
                            $classify[$item->psku_id] = ProductSku::getValueById($item->psku_id,'classify_id');
                        }
                        $classifyId = $classify[$item->psku_id];
                        $key = $item->country_id .'_'. $classifyId;
                        if(!array_key_exists($key,$exist)){
                            $exist[$key] = self::_getReturn($item->country_id,$classifyId);
                        }
                        if(floatval($exist[$key]) == 0)continue;
                        $tpl = round($item->tpl_rmb / $item->rate_odds,3);
                        $item->return = $exist[$key];
                        $item->cost = ProfitSku::getCost($tpl,$item->sale_price,$item->head_cost,$item->fba_fee,$item->commission,$item->return,$item->vat);
                        $item->profit = round($item->sale_price - $item->cost,3);
                        $item->profit_rate = $item->sale_price == 0 ? 0 : round($item->profit / $item->sale_price,3);
                        $item->save();
                    }
                    DB::commit();
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }

    private static function _getReturn($countryId,$classifyId){
        $salesVolume = PaymentClassify::query()
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->sum('sales_volume');
        if($salesVolume == 0)return 0;
        $refundNum = PaymentClassify::query()
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->sum('refund_num');
        if($refundNum == 0)return 0;
        return round($refundNum / $salesVolume,5) * 100;
    }

    /**
     * 同步物流费
     */
    public static function syncLogistics(){
        ini_set('memory_limit', '200M');
        set_time_limit(0);
        $area = [];
        $avgFeeArr = [];
        $shipmentArr = [];
        $transportArr = [];
        ProfitSku::query()
            ->orderBy('id')
            ->chunk(800, function ($items) use (&$area,&$avgFeeArr,&$shipmentArr,&$transportArr){
                DB::beginTransaction();
                try{
                    $sql = "UPDATE operation_profit_sku SET ";
                    $idArr = [];
                    $sql1 = "head_cost = CASE id ";
                    $sql2 = "cost = CASE id ";
                    $sql3 = "profit = CASE id ";
                    $sql4 = "profit_rate = CASE id ";
                    foreach ($items as $item){
                        if(!array_key_exists($item->country_id,$area)){
                            $area[$item->country_id] = SysICountry::getAreaIdByCountryId($item->country_id);
                        }
                        $areaId = $area[$item->country_id];
                        $shipmentKey = $areaId .'_'. $item->psku_id;
                        if(!array_key_exists($shipmentKey,$avgFeeArr)){
                            $shipmentCount = LogisticsBasics::countLastLogistics($areaId,$item->psku_id);
                            $avgFeeArr[$shipmentKey] = $shipmentCount > 0 ? true : false;
                        }
                        $avgFee = $avgFeeArr[$shipmentKey];
                        $transportKey = $areaId .'_'. $item->transport;
                        if($avgFee){
                            if(!array_key_exists($shipmentKey,$shipmentArr)){
                                $shipmentArr[$shipmentKey] = LogisticsBasics::getLastLogistics($areaId,$item->psku_id);
                            }
                            $shipment = $shipmentArr[$shipmentKey];
                            $logisticsFee = $shipment->price;
                            $shipWeight = 1;
                        }else{
                            if(!array_key_exists($transportKey,$transportArr)){
                                $transportArr[$transportKey] = Transport::getShipmentFee($areaId,$item->transport);
                            }
                            $logisticsFee = $transportArr[$transportKey];
                            $shipWeight = $item->weight;
                        }
                        $headCost = round($shipWeight * $logisticsFee / $item->rate_odds,3);
                        $tpl = round($item->tpl_rmb / $item->rate_odds,3);
                        $cost = ProfitSku::getCost($tpl,$item->sale_price,$headCost,$item->fba_fee,$item->commission,$item->return,$item->vat);
                        $profit = round($item->sale_price - $cost,3);
                        $profitRate = $item->sale_price == 0 ? 0 : round($profit / $item->sale_price,3);
                        $sql1 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $headCost);
                        $sql2 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $cost);
                        $sql3 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $profit);
                        $sql4 .= sprintf("WHEN %d THEN %0.3f ", $item->id, $profitRate);
                        $idArr[] = $item->id;
                    }
                    if(!empty($idArr)){
                        $ids = implode(',',$idArr);
                        $sql1 .= "END,";
                        $sql2 .= "END,";
                        $sql3 .= "END,";
                        $sql4 .= "END";
                        $sql .= $sql1 . $sql2 . $sql3 . $sql4 . " WHERE id IN ($ids)";
                        DB::update(DB::raw($sql));
                    }
                    DB::commit();
                    sleep(10);
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
            });
    }
}
